package com.szholly.pro.watergas_server.ps.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.ITable;
import com.szholly.data.general.PageInfo;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;

/**
 * 现场审核Service
 * @author
 *
 */
@Service
public class StationCheckinfoService {
	
	/**
	 * 现场考核信息查询
	 * @param params
	 * @param pTable
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> queryStationCheckList(Map<String, String> params, ITable pTable, PageInfo pageInfo) throws Exception {
		List<GlobalParameter> listParam = new ArrayList<>();
		String sql="select ms.code,su.*,(case"
	        +" when su.check_status is null then '提交保存'"          
	        +" when su.check_status ='0' then  '待审核'"         
	        +" when su.check_status ='1' then   '审核通过'"        
	        +" when su.check_status ='2' then '未通过'"
	        +"  when su.check_status ='3' then '提交保存'"	       
	        +"   end) as STATUS,"
	        +"(case when su.pH='0' then '否' when su.pH ='1' then '是' when su.pH is null then '-' end ) as pHl,"
	        +"(case when su.totalm='0' then '否' when su.totalm ='1' then '是' when su.totalm is null then '-' end ) as total,"
	        +"(case when su.turbidit='0' then '否' when su.turbidit ='1' then '是' when su.turbidit is null then '-' end ) as turbidity,"
	        +"(case when su.chlorine='0' then '否' when su.chlorine ='1' then '是' when su.chlorine is null then '-' end ) as chlorined,"
	        +"(case when su.is_data_tran_work='0' then '否' when su.is_data_tran_work ='1' then '是' when su.is_data_tran_work is null then '-' end ) as isdatatranWork," 
	        +"(case when su.is_house_clean='0' then '否' when su.is_house_clean ='1' then '是' when su.is_house_clean is null then '-' end ) as ishouseclean, "
	        +"(case when su.is_operation_work='0' then '否' when su.is_operation_work ='1' then '是' when su.is_operation_work is null then '-' end ) as isoperationwork, "
	        +"substr(ms.name,instr(ms.name,'、')+1,length(ms.name)-instr(ms.name,'、')) as NAME,us.sign_img as auditorimg,pro.sign_img as proimg,us.c_userealname,pro.c_userealname as proNmae,uing.c_userealname as INSPECTORIMG "
	        +" ,t2.c_userealname as companyimg,pro.s_dw,nvl(su.CHECK_POINT,'-') as checkpoint, nvl(to_char(su.CHECK_DATE,'YYYY-MM-DD HH24:MI:SS')||'','-') as checkdata from BC_SITE_INFO ms left join (select * from supply_water_station_check su1 left join (select s_domaincaption, check_id,is_pass from supply_water_station_checkitem si "
	        + "left join DATA_DEFULATDOMAIN de on si.poll_code = de.s_domaincode and de.s_domainname = '深圳供水参数' ) pr "
            +" on su1.guid=pr.check_id pivot (sum(is_pass) for s_domaincaption in ('pH' pH, '总氯' totalm, '浊度' turbidit ,'二氧化氯' chlorine))) su on ms.code=su.station_code ";
            if (params.containsKey("start")) {//开始时间
    			sql += " and su.CHECK_DATE >= to_date('" + params.get("start")
    					+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
    		}
    		if (params.containsKey("end")) {//结束时间
    			sql += " and su.CHECK_DATE <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
    		}
	        sql +=" left join safe_user us on us.s_userid = su.auditor"//审核人
	        +" left join safe_user pro on pro.s_userid=su.proposer "//申请人
	        +" left join safe_user uing on uing.s_userid=su.INSPECTOR_USERID "//检查人员
	        +"  left join safe_user t2 on t2.s_userid=su.company_userid "//公司人员
	        +" where 1=1 ";
		
		if(params.containsKey("station_id")) {
			sql += " and ms.code='"+params.get("station_id")+"'";
		}
		if(params.containsKey("status")) {
			sql += " and su.check_status='"+params.get("status")+"'";
		}
		if(params.containsKey("checkid")) {
			sql += " and su.guid='"+params.get("checkid")+"'";
		}
		sql+=" order by su.CHECK_POINT asc ,to_number(ms.code) asc";
		if(pTable==null&&pageInfo==null){
			return DataUtil.getSelectRows(sql, listParam);
		}else{
			return DataUtil.getPageRow(sql, listParam, pTable, pageInfo);
		}
	}
	/**
	 * 获取供水总的站点数量
	 */
	public List<RowBase> getTotalStation(Map<String, String> params) throws Exception {
		String sql="select count(name) as stationnum from BC_SITE_INFO ";
		
		return DataUtil.getSelectRows(sql, null);
	}
	/**
	 * 获取站点数量
	 * @param params
	 */
	public List<RowBase> getStationNum(Map<String, String> params) throws Exception {
		String sql="select count(ms.name) as totalstation,sum(su.check_point) as totalpoints from (select * from supply_water_station_check su1 left join (select s_domaincaption, check_id,is_pass from supply_water_station_checkitem si "
		        + "left join DATA_DEFULATDOMAIN de on si.poll_code = de.s_domaincode and de.s_domainname = '深圳供水参数' ) pr "
	            +" on su1.guid=pr.check_id pivot (sum(is_pass) for s_domaincaption in ('pH' pH, '总氯' totalm, '浊度' turbidit ,'二氧化氯' chlorine))) su "
		        +" left join BC_SITE_INFO ms on ms.code=su.station_code "
		        +" left join safe_user us on us.s_userid = su.auditor"//审核人
		        +" left join safe_user pro on pro.s_userid=su.proposer "//申请人
		        +" left join safe_user uing on uing.s_userid=su.INSPECTOR_USERID "//检查人员
		        +"  left join safe_user t2 on t2.s_userid=su.company_userid "//公司人员
		        +" where 1=1 ";
		List<GlobalParameter> listParam = new ArrayList<>();
		if (params.containsKey("start")) {//开始时间
			sql += " and su.CHECK_DATE >= to_date('" + params.get("start")
					+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if (params.containsKey("end")) {//结束时间
			sql += " and su.CHECK_DATE <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		return DataUtil.getSelectRows(sql, listParam);
	}
	/**
	 *现场考核信息修改
	 * @param params
	 * @throws Exception
	 */
	public void updateStationCheck(HashMap<String, Object> params,ITable pTable)throws Exception{	
		DataUtil.Update(pTable.getTableName(), params);
	}
	/**
	 * 现场考核详情信息获取
	 * @param params
	 * @param pTable
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> queryStationCheckItemList(Map<String, String> params, ITable pTable) throws Exception {
		String sql="select sp.*,de.s_domaincaption as DOMAINCAPTION,"
				+" (case  when sp.is_pass ='0' then '否' when sp.is_pass='1' then '是' else sp.is_pass end) as ispass "
		         +" from supply_water_station_checkitem sp"
				 +" left join supply_water_station_check t1 on t1.guid=sp.check_id"
		         +" left join DATA_DEFULATDOMAIN de on sp.poll_code=de.s_domaincode"
		         +" where de.s_domainname='深圳供水参数' ";
		if(params.containsKey("checkid")){
			sql+=" and sp.check_id='"+params.get("checkid")+"'";
		}
		if (params.containsKey("start")) {//开始时间
			sql += " and t1.CHECK_DATE >= to_date('" + params.get("start")
					+ " 00:00:00', 'YYYY-MM-DD HH24:MI:SS')";
		}
		if (params.containsKey("end")) {//结束时间
			sql += " and t1.CHECK_DATE <= to_date('" + params.get("end") + " 23:59:59', 'YYYY-MM-DD HH24:MI:SS')";
		}
		List<GlobalParameter> listParam=new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam, pTable);
	}
	/**
	 * 获取检测人员
	 * @param params
	 * @return
	 * @throws Exception
	 */
	public List<RowBase> queryOperationPersonList(Map<String, String> params,ITable pTable) throws Exception {

		String sql="select c_userealname,s_userid from safe_user where s_orgname='水质检测中心' ";
		List<GlobalParameter> listParam=new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam, pTable);
	}
	/**
	 * 获取公司运维人员
	 * @param params
	 * @param pTable
	 * @return
	 */
	public List<RowBase> queryOperationNmaeList(Map<String, String> params,ITable pTable){
		String sql="select u.s_userid,u.c_username,u.c_userealname,"
				  +"  r.c_rolename from safe_user u left join safe_userrole ur on u.s_userid = "
                  +" ur.s_userid left join safe_role r on r.i_roleid = ur.s_roleid where r.c_rolename='公司运营人员'";
		List<GlobalParameter> listParam=new ArrayList<>();
		return DataUtil.getSelectRows(sql, listParam, pTable);
	}
	/**
	 * 现场考核详情修改
	 * @param params
	 * @throws Exception
	 */
	public void updateStationCheckItem(ITable pTable,HashMap<String, Object> params)throws Exception{		
		//执行修改之前，先把原先的记录删除	
		DataUtil.Update(pTable.getTableName(), params);//执行新增方法
	}
	/**
	 * 现场考核详情信息删除
	 * @param params
	 * @throws Exception
	 */
	public void deleteStationCheckItem(HashMap<String, Object> params)throws Exception{
		String sql="delete supply_water_station_checkitem su where su.check_id='"+params.get("checkData[GUID]")+"' ";
		DataUtil.ExecuteUpdateSqlInTransaction(sql, null);//执行SQL语句
	}
}
